<?php
/**
 * Created by PhpStorm.
 * User: admin
 * Date: 2016/9/21
 * Time: 9:47
 * 产品入库
 */
date_default_timezone_set('PRC');
define('ENV', 'test');
switch (ENV) {
    case 'dev':
        define('SERVER', '127.0.0.1');
        define('PORT', '3307');
        define('DBNAME', 'entry_ms');
        define('DBUSER', 'root');
        define('PWD', 'usbw');
        define('CHARSET', 'uft8');
        break;
    case 'test':
        define('SERVER', '127.0.0.1');
        define('PORT', '3306');
        define('DBNAME', 'entry_ms');
        define('DBUSER', 'root');
        define('PWD', 'jh!@#2016');
        define('CHARSET', 'uft8');
        break;
    case 'prod':
        define('SERVER', '127.0.0.1');
        define('PORT', '3306');
        define('DBNAME', 'entry_ms');
        define('DBUSER', 'root');
        define('PWD', 'jh!@#2016');
        define('CHARSET', 'uft8');
        break;
    default:
        break;
}
$request_data = $_REQUEST;
$ck_date = $request_data['ck_date'];
$rk_date = $request_data['rk_date'];
$model = $request_data['model'];
$download = $request_data['download'];

if ($download == '1') {
    header('Content-Type: text/html;charset=utf-8');
    $search_str = " WHERE 1=1 ";
    if ($model != '') {
        $search_str .= "  AND products_model = '$model'";
    }


    if ($ck_date != '' && $rk_date != '') {
        if (strtotime($rk_date) > time() || strtotime($rk_date) > time()) {
            OSAdmin::alert('error', '查询時間只能相对当前时间往前');
        }

        if (((time() - strtotime($rk_date)) > 30 * 24 * 3600) || ((time() - strtotime($ck_date)) > 30 * 24 * 3600)) {
            OSAdmin::alert('error', '查询時間只能往前且不能超過30天');
        }

        $cktime = strtotime($ck_date);
        $rktime = strtotime($rk_date);
        $search_str .= " AND UNIX_TIMESTAMP(products_due_date) >= $cktime AND UNIX_TIMESTAMP(products_add_date) >= $rktime";
    } elseif ($rk_date != '' && $ck_date == '') {
        if (strtotime($rk_date) > time()) {
            OSAdmin::alert('error', '查询時間只能相对当前时间往前');
        }

        if (((time() - strtotime($rk_date)) > 30 * 24 * 3600)) {
            OSAdmin::alert('error', '查询時間只能往前且不能超過30天');
        }

        $rktime = strtotime($rk_date);
        $search_str .= " AND UNIX_TIMESTAMP(products_add_date) >= $rktime";
    } elseif ($rk_date == '' && $ck_date != '') {
        if (strtotime($ck_date) > time()) {
            OSAdmin::alert('error', '查询時間只能相对当前时间往前');
        }

        if ((time() - strtotime($ck_date)) > (30 * 24 * 3600)) {
            OSAdmin::alert('error', '查询時間只能往前且不能超過30天');
        }
        $cktime = strtotime($ck_date);
        $search_str .= " AND UNIX_TIMESTAMP(products_due_date) >= $cktime";
    }

    $products = getAllBySearchNoLimit($search_str);
    if ($products) {
//    $products = AdminProducts::getAllProducts();
        foreach ($products as $product) {
            $np['products_id'] = $product['products_id'];
            $np['products_name'] = $product['products_name'];
            $np['products_model'] = $product['products_model'];
            $np['products_price'] = $product['products_price'];
            $np['products_currency'] = $product['products_currency'];
            $np['products_description'] = $product['products_description'];
            $np['products_sale_num'] = $product['products_sale_num'];
            $np['products_add_date'] = $product['products_add_date'];
            if ($product['products_add_status'] == 1) {
                $np['products_add_status'] = "正常入库";
            } elseif ($product['products_add_status'] == 2) {
                $np['products_add_status'] = "退货再入库";
            } else {
                $np['products_add_status'] = "换货再入库";
            }

            $np['products_sale_other'] = $product['products_sale_other'];
            $np['products_due_date'] = $product['products_due_date'];

            if ($product['products_sale_other'] > 0) {
                if ($product['products_due_status'] == 1) {
                    $np['products_due_status'] = "正常出库";
                } else {
                    $np['products_due_status'] = "换货再出库";
                }
            } else {
                $np['products_due_status'] = "无";
            }


            if ($product['products_evaluate'] == 1) {
                $np['products_evaluate'] = "已评估";
            } else {
                $np['products_evaluate'] = "未评估";
            }


            $time = time() - strtotime($product['products_add_date']);
            if (($time <= 10 * 24 * 3600) && $product['products_add_status'] == '1') {
                $np['products_new'] = "是";
            } else {
                $np['products_new'] = "否";
            }


            $num = getTenDaysSaleOtherNums();
            if (!$num['sum']) {
                $np['avge'] = "0%";
            } else {
                $np['avge'] = sprintf('%.2f', intval($num['sum'])/ 10) * 100;
                $np['avge'] = $np['avge'] . "%";
            }

            $nps[] = $np;
        }
    } else {
        $nps = array();
    }


    require_once dirname(__FILE__) . '/../include/lib/PHPExcelClasses/PHPExcel.php';
    $fileName_s = "库存产品详情表";
    $headArr_s = array(
        '产品编号',
        '产品名称',
        '产品model号',
        '产品进货价',
        '进货货币类型',
        '产品描述',
        '入库量',
        '入库时间',
        '入库状态',
        '出库量',
        '出库时间',
        '出库状态',
        '是否评估',
        '近十天是否新品',
        '近十天出库平均值');
    $data_source = $nps;

    getExcel($fileName_s, $headArr_s, $data_source);


}

function getExcel($fileName, $headArr, $data)
{
    if (empty($data) || !is_array($data)) {
        die("data must be a array");
    }
    if (empty($fileName)) {
        exit;
    }
    $date = date("Y_m_d", time());
    $fileName .= "_{$date}.xlsx";

    //创建新的PHPExcel对象
    $objPHPExcel = new PHPExcel();
    $objProps = $objPHPExcel->getProperties();

    //设置表头
    $key = ord("A");
    foreach ($headArr as $v) {
        $colum = chr($key);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);
        $key += 1;
    }

    $column = 2;
    $objActSheet = $objPHPExcel->getActiveSheet();
    foreach ($data as $key => $rows) { //行写入
        $span = ord("A");
        foreach ($rows as $keyName => $value) {// 列写入
            $j = chr($span);
            $objActSheet->setCellValue($j . $column, $value);
            $span++;
        }
        $column++;
    }

    $fileName = iconv("utf-8", "gb2312", $fileName);
    //重命名表
    $objPHPExcel->getActiveSheet()->setTitle('Simple');
    //设置活动单指数到第一个表,所以Excel打开这是第一个表
    $objPHPExcel->setActiveSheetIndex(0);
    //将输出重定向到一个客户端web浏览器(Excel2007)
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header("Content-Disposition: attachment; filename=\"$fileName\"");
    header('Cache-Control: max-age=0');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

    $objWriter->save('php://output'); //文件通过浏览器下载

    exit;


}

function getAllBySearchNoLimit($condition_str)
{
    try {
        $pdo = null;
        $pdo = new PDO('mysql:host=' . SERVER . ';port=' . PORT . ';dbname=' . DBNAME, DBUSER, PWD);
        $pdo->setAttribute(PDO::ATTR_PERSISTENT, true);
        $pdo->exec('SET NAMES utf8');
        $sql = "SELECT * FROM kc_infos $condition_str";
        $all = $pdo->query($sql)->fetchAll();
        return $all;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}

function getTenDaysSaleOtherNums()
{
    try {
        $pdo = null;
        $pdo = new PDO('mysql:host=' . SERVER . ';port=' . PORT . ';dbname=' . DBNAME, DBUSER, PWD);
        $pdo->setAttribute(PDO::ATTR_PERSISTENT, true);
        $pdo->exec('SET NAMES utf8');

        $sql = "SELECT SUM(products_sale_other) as sum FROM kc_infos WHERE TO_DAYS(CURDATE()) - TO_DAYS(DATE_FORMAT(products_due_date,'%Y-%m-%d')) <=10;";
        $ret = $pdo->query($sql)->fetch();
        return $ret;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}